/**
*@项目名称: frt-api
*@文件名称: PoiUtils.java
*@Date: 2018年11月19日
*@Copyright: 2018 www.abing.tech Inc. All rights reserved.
*/

package io.renren.modules.frt.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.baomidou.mybatisplus.toolkit.StringUtils;

import io.renren.modules.frt.entity.DistrictEntity;
import io.renren.modules.frt.entity.MonitorExcelEntity;
import io.renren.modules.frt.entity.MonitorHisEntity;

/**
 * poi操作工具类
 * @author qibing.huang
 * @date 2018年11月19日
 */
public class PoiUtils {

	/**
	 *  
	 * @param args void
	 */
	public static void main(String[] args) {
		try {
			//PoiUtils.parseExcel(new FileInputStream("E:\\abing\\forestry\\sources\\frt-api\\src\\main\\resources\\static\\data\\表1：全国林业产业发展监测统计表.xlsx"));
			
			//PoiUtils.exportExcel("E:\\abing\\forestry\\sources\\frt-api\\src\\main\\resources\\static\\data\\test.xlsx");
			
			List<DistrictEntity> disList = PoiUtils.parseDistrict(new FileInputStream("E:\\abing\\forestry\\sources\\frt-api\\src\\main\\resources\\static\\data\\行政区划数据库_with+经纬度-省市区-邮编-区号-拼音-简称.xls"));
			for(DistrictEntity dis : disList) {
				System.out.println(ToStringBuilder.reflectionToString(dis));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static List<MonitorExcelEntity> parseMonitor(InputStream inStream) {
   	    //创建工作簿
		Workbook  wb = null;
    	if(! inStream.markSupported()) {
    		inStream = new PushbackInputStream(inStream, 8);
		}
		try {
			if(POIFSFileSystem.hasPOIFSHeader(inStream)) {
				wb = new HSSFWorkbook(inStream);
			}
			if(DocumentFactoryHelper.hasOOXMLHeader(inStream)) {
				wb = new XSSFWorkbook(OPCPackage.open(inStream));
			}
		} catch (InvalidFormatException e) {
		} catch (IOException e) {
		}
    	//读取第一个sheet
    	Sheet sheet = wb.getSheetAt(0);
    	
    	int rows = sheet.getLastRowNum();
    	List<MonitorExcelEntity> list = new ArrayList<>();
    	for(int i = 3 ;i < rows -1; i++) {
    		Row row = sheet.getRow(i);
    		int cells = row.getLastCellNum();
    		if(cells > 5) {
    			MonitorExcelEntity entity = new MonitorExcelEntity();
        		
    			entity.setIndexName(row.getCell(0).toString());
    			if(row.getCell(1) != null && StringUtils.isNotEmpty(row.getCell(1).toString())) {
    				entity.setIndexCode(String.valueOf((int)Double.parseDouble(row.getCell(1).toString())));
    			}
    			entity.setIndexUnit(row.getCell(2).toString());
    			entity.setIndexRemark(row.getCell(5).toString());
        		
        		list.add(entity);
    		}
    		
    	}
    	return list;
	}
	
	public static List<MonitorHisEntity> parseMonitorHis(InputStream inStream, String fileId) {
   	    //创建工作簿
		Workbook  wb = null;
    	if(! inStream.markSupported()) {
    		inStream = new PushbackInputStream(inStream, 8);
		}
		try {
			if(POIFSFileSystem.hasPOIFSHeader(inStream)) {
				wb = new HSSFWorkbook(inStream);
			}
			if(DocumentFactoryHelper.hasOOXMLHeader(inStream)) {
				wb = new XSSFWorkbook(OPCPackage.open(inStream));
			}
		} catch (InvalidFormatException e) {
		} catch (IOException e) {
		}
    	//读取第一个sheet
    	Sheet sheet = wb.getSheetAt(0);
    	
    	int rows = sheet.getLastRowNum();
    	List<MonitorHisEntity> list = new ArrayList<>();
    	for(int i = 3 ;i < rows -1; i++) {
    		Row row = sheet.getRow(i);
    		int cells = row.getLastCellNum();
    		if(cells > 5) {
    			MonitorHisEntity entity = new MonitorHisEntity();
        		
    			entity.setIndexName(row.getCell(0).toString());
    			if(row.getCell(1) != null && StringUtils.isNotEmpty(row.getCell(1).toString())) {
    				entity.setIndexCode(String.valueOf((int)Double.parseDouble(row.getCell(1).toString())));
    			}
    			entity.setIndexUnit(row.getCell(2).toString());
    			if(row.getCell(3) != null && StringUtils.isNotEmpty(row.getCell(3).toString())) {
    				entity.setYear1(new BigDecimal(row.getCell(3).toString()));
    			}
    			if(row.getCell(4) != null && StringUtils.isNotEmpty(row.getCell(4).toString())) {
    				entity.setYear2(new BigDecimal(row.getCell(4).toString()));
    			}
    			
    			entity.setIndexRemark(row.getCell(5).toString());
    			
    			entity.setFileId(fileId);
        		
        		list.add(entity);
    		}
    		
    	}
    	return list;
	}
	
	public static List<DistrictEntity> parseDistrict(InputStream inStream) {
   	    //创建工作簿
		Workbook  wb = null;
    	if(! inStream.markSupported()) {
    		inStream = new PushbackInputStream(inStream, 8);
		}
		try {
			if(POIFSFileSystem.hasPOIFSHeader(inStream)) {
				wb = new HSSFWorkbook(inStream);
			}
			if(DocumentFactoryHelper.hasOOXMLHeader(inStream)) {
				wb = new XSSFWorkbook(OPCPackage.open(inStream));
			}
		} catch (InvalidFormatException e) {
		} catch (IOException e) {
		}
    	//读取第一个sheet
    	Sheet sheet = wb.getSheetAt(0);
    	
    	int rows = sheet.getLastRowNum();
    	List<DistrictEntity> districtList = new ArrayList<>();
    	for(int i = 1 ;i < rows; i++) {
    		Row row = sheet.getRow(i);
    		int cells = row.getLastCellNum();
    		if(cells > 5) {
    			DistrictEntity district = new DistrictEntity();
        		
        		district.setCode(row.getCell(0).toString());
        		district.setName(row.getCell(1).toString());
        		district.setParentCode(row.getCell(2).toString());
        		district.setLevel((int)Double.parseDouble(row.getCell(4).toString()));
        		
        		districtList.add(district);
    		}
    		
    	}
    	return districtList;
	}
	
	public static void parseExcel(InputStream inStream) {
   	    //创建工作簿
    	XSSFWorkbook wb = null;
		try {
			wb = new XSSFWorkbook(inStream);
		} catch (IOException e) {
			e.printStackTrace();
			return;
		} 
    	//读取第一个sheet
    	Sheet sheet = wb.getSheetAt(0);
    	
    	int rows = sheet.getLastRowNum();
    	for(int i = 0 ;i < rows; i++) {
    		Row row = sheet.getRow(i);
    		int cells = row.getLastCellNum();
    		for(int j = 0; j < cells; j++) {
    			System.out.print(row.getCell(j));
    			System.out.print("|空|");
    		}
    		System.out.println();
    	}
	}
	
	public static void exportExcel(String excelName) {
		
			//创建工作簿
			XSSFWorkbook wb = new XSSFWorkbook();
			//创建一个sheet
			XSSFSheet sheet = wb.createSheet();
	 
			// 创建单元格样式
			XSSFCellStyle style =  wb.createCellStyle();	
			style.setFillForegroundColor((short)4); //设置要添加表格北京颜色
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND); //solid 填充
			style.setAlignment(XSSFCellStyle.ALIGN_CENTER); //文字水平居中
			style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//文字垂直居中
			style.setBorderBottom(BorderStyle.THIN); //底边框加黑
			style.setBorderLeft(BorderStyle.THIN);  //左边框加黑
			style.setBorderRight(BorderStyle.THIN); // 有边框加黑
			style.setBorderTop(BorderStyle.THIN); //上边框加黑
			//为单元格添加背景样式
			for (int i = 0; i < 6; i++) { //需要6行表格
			    Row  row =	sheet.createRow(i); //创建行
				for (int j = 0; j < 6; j++) {//需要6列
					row.createCell(j).setCellStyle(style);
				}
			}
			
			
			//合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//合并单元格，cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 5));
			
			//tian入数据
			XSSFRow row = sheet.getRow(0); //获取第一行
			row.getCell(1).setCellValue("2018期末考试"); //在第一行中创建一个单元格并赋值
			XSSFRow row1 = sheet.getRow(1); //获取第二行，为每一列添加字段
			row1.getCell(1).setCellValue("语文");
			row1.getCell(2).setCellValue("数学");
			row1.getCell(3).setCellValue("英语");
			row1.getCell(4).setCellValue("物理");
			row1.getCell(5).setCellValue("化学");
			XSSFRow row2 = sheet.getRow(2); //获取第三行
			row2.getCell(0).setCellValue("张三");
			XSSFRow row3 = sheet.getRow(3); //获取第四行
			row3.getCell(0).setCellValue("张三");
			XSSFRow row4 = sheet.getRow(4); //获取第五行
			row4.getCell(0).setCellValue("张三");
			XSSFRow row5 = sheet.getRow(5); //获取第五行
			row5.getCell(0).setCellValue("张三");
			//将数据写入文件
			FileOutputStream out;
			try {
				out = new FileOutputStream(excelName);
				try {
					wb.write(out);
				} catch (IOException e) {
					e.printStackTrace();
				}
			} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static void parseWord(InputStream inStream) {
		
	}

}
